如何管理 SQL-based pipeline？

整理完當初我們遇到的問題，要開始來談談 dbt 怎麼解決問題了。
不過，實際進入到工具之前，我們先從 code 的管理，來思考這件事吧？

什麼是 CTE (Common Table Expression)？

CTE 是一個暫存的檢視 (view)，可以把一個 select statement 從 sql 中抓出來，作為獨立的 view 讓之後的 sql code 可以直接引用。
可以抽象的理解為：在真正的切菜（join table）之前，先備料（data pre-processing）準備好。

在一個 sql-based 的 ETL pipeline 中，除了常見的用 git 做版本控制之外，另一個重點是我們如何管理每一段 sql code？對我來說，CTE（取代了 sub-queries）中良好的命名，其實是非常適合將一段 200 行 sql 快速翻譯的好方法。

WITH
  -- 準備 7 & 8 年級的 cover range
  cover_range_filtered_by_7th8th AS(
  SELECT
    grade,
    SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(cover_range, "[", ""), "]", ""), ",", ""), "\"", ""), " ") AS cover_range_array
  FROM
    `junyiacademy.datastore_backup.Exercise`
  WHERE
    (grade = 7
      OR grade = 8)
    AND LENGTH(cover_range) > 3),
  unnest_cover_range_of_exercise_into_rows AS(
  SELECT
    grade,
    cover_range
  FROM
    cover_range_filtered_by_7th8th,
    UNNEST(cover_range_array) AS cover_range ),
  -- 準備數學科的 quiz
  questions_extracted_from_json AS(
  SELECT
    quiz_id,
    JSON_EXTRACT(question, '$.question.') AS question,
    SPLIT(cover_range_list, ",") AS cover_range_array,
  FROM
    `junyiacademy.IntegratedTable.Info_PerseusQuiz`
  WHERE
    subject = "數學"
    AND JSON_EXTRACT(question, '$.question.') IS NOT NULL ),
  cover_range_unnest_into_rows AS(
  SELECT
    quiz_id,
    question,
    cover_range,
  FROM
    questions_extracted_from_json,
    UNNEST(cover_range_array) AS cover_range ),
  -- 篩選出 7 & 8 年級的數學題
  questions_filtered_by_cover_range AS(
  SELECT
    quiz_id,
    question,
    STRING_AGG(cover_range) AS cover_range_list,
  FROM
    cover_range_unnest_into_rows
  WHERE
    cover_range IN (
    SELECT
      cover_range
    FROM
      unnest_cover_range_of_exercise_into_rows)
  GROUP BY
    quiz_id,
    question)
SELECT
  *
FROM
  questions_filtered_by_cover_range

（真不習慣沒有 toggle 的世界⋯⋯XD）

先整理出我的註解 & CTE naming：

• 準備 7 & 8 年級的 cover range
  • cover_range_filtered_by_7th8th
  • unnest_cover_range_of_exercise_into_rows
• 準備數學科的 quiz
  • questions_extracted_from_json
  • cover_range_unnest_into_rows
• 篩選出 7 & 8 年級的數學題
  • questions_filtered_by_cover_range

我通常會用名詞+子句的方式，說明這張臨時的 view 具體在做的事情是什麼。而兩三個 CTE 會有一個具體的任務目標，則可以透過註解的方式包裹起來，說明準備的目的。在上述的 code 中，分成三個步驟，每一個步驟內再具體分為兩到三個 CTE 來處理。

為什麼 CTE-naming 可以解決管理問題？

參考 python / c++ clean code 的邏輯（只是 naming 的部分！），其實 sql 也可以很好的做到 code as doc 的梳理，讓閱讀 code 的人可以輕鬆的理解這個 sql code 究竟處理了什麼事情 / 如何處理。
問題是，若有某一個很特別的 CTE 同時被下游需要的時候（例如，對於 user_id 的重新編碼，每一個下游表都要做一次），我們通常會做個中繼表來處理（似乎是個好方法）。
但越來越多的中繼表，不只會讓 sql file 越來越多（產生管理問題），也會讓我們要理解一個 model 的時候，需要看太多的 sql code，反而導致不易追查。

dbt 的核心，其實就是把每一個 CTE 轉換成好理解的方式（圖像化 lineage），組合為一個單獨的 model，來協助 analytic engineers 面對這個問題！